﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[Proc_Order_SelectClaim_Old]
	
	(
		@TypeId INT ,--首款尾款
		@CusName NVARCHAR(1000),--客户名称
		@StartDate VARCHAR(10),--认领时间
		@EndDate VARCHAR(10),  --认领时间                       
		@CompanyId VARCHAR(500),--单位ID
		@DepId INT, --部门ID
		@NOE_Flag INT,
		@StartIndex INT,
		@EndIndex INT,
		@SWId int,
		@PaymentTypeId int,
		@BankId int,
		@checkxj bit,
		@rblbank INT
	)
AS
BEGIN
	DECLARE @str NVARCHAR(4000),@Sqlstr NVARCHAR(4000)
	set @str =''
	IF @TypeId <>0
	BEGIN
		SET @str =' o.TypeId='+CONVERT(VARCHAR,@TypeId)+' and '
	END
	IF @SWId <>0
	BEGIN
		SET @str =@str+' opr.SWId='+CONVERT(VARCHAR,@SWId)+' and '
	END
	
	IF @CompanyId<>''
	BEGIN
		SET @str=@str+' opr.CompanyId in ('+@CompanyId+') and  '
	END
	
	--IF @StartDate<>'' AND @EndDate<>''
	--BEGIN
	--	SET @str=@str+ ' Convert(varchar(10), opr.FinanceDate,120) between '''+@StartDate+''' and '''+@EndDate+''' and  '
	--END
	
	IF @DepId <>0
	BEGIN
		SET @str=@str+' o.DepId in (SELECT id FROM (
 SELECT ( SELECT	d.bmbh FROM	Department d WHERE id='+CONVERT(VARCHAR,@DepId)+') AS a) AS nb1,Department 
WHERE LEFT(bmbh,LEN(a))=a) and '
	END
	
	
	IF @NOE_Flag<>100
	BEGIN
		SET @str=@str+ ' o.NOE_Flag='+CONVERT(VARCHAR,@NOE_Flag)+' and '
	END
	
	
		IF @PaymentTypeId=1 --现金
BEGIN   --查现金时，还需要查询电汇中需要算在现金中的电汇账号
    IF @checkxj=0
    BEGIN
    	SET @str=@str+'opr.PaymentTypeId=1  and '
    END
    ELSE 
    	BEGIN
    		SET @str=@str+'(opr.PaymentTypeId=1 
	                  or opr.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid in ('+ @CompanyId+'))) and '
    	END
END
ELSE IF @PaymentTypeId=2 --银行
BEGIN
	IF  @BankId=0 --电汇账号选择为全部时，查询所有算在银行中的电汇金额
	BEGIN
		SET @str=@str+'opr.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+' and Companyid in ('+ @CompanyId+')) and '
	END
	ELSE
		BEGIN--选择了电汇账号时，查询该账号下的金额
			SET @str=@str+'opr.BankId = (select id from Finance_Bank where id='+CONVERT(VARCHAR, @BankId)+' and  PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+') and '
		END
END
ELSE IF @PaymentTypeId=3 --支票
BEGIN
	SET @str=@str+'(opr.PaymentTypeId=3 ) and '
END
--ELSE
--	BEGIN
--		set @str=@str+' opr.PaymentTypeId in(1,2,3)  and '
--	END
	IF @CusName<>''
	BEGIN
		SET @str=@str+' opr.cusname like ''%'+@CusName+'%'' and '
	END
	
	
	IF @str<>''
	BEGIN
		set @str=' and '+@str--SUBSTRING(@str,1,LEN(@str)-3)
	END
	
	SET @Sqlstr='
	
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	
	
	
	;WITH list As(  SELECT ROW_NUMBER() OVER (ORDER BY FinanceDate desc)AS Row,
		opr.Id,
		opr.GId,
		opr.RId,
		opr.OId,
		opr.CusId,
		opr.CusName,
		opr.SalesmanAccount,
		opr.Salesman,
		opr.CreateDate,
		opr.Prices,
		opr.PaymentTypeId,
		opr.PaymentType,
		opr.BankId,
		opr.Bank,
		opr.SWId,
		opr.SWName,
		opr.shjbh,
		opr.FinanceFlag,
		opr.FinanceDate,
		opr.CompanyId,
		opr.[Description]
		,o.NOE_Flag, o.IsGreen, o.DepId,o.YingShouPrice
		,(case NOE_Flag when 1 then ''[非企]'' when 2 then ''[非常规]'' else '''' end ) as NOE_Flag_Name
		,o.TypeName,
		IsNull((Select Title From Order_State Where Id=o.StateId),''---'') As StateName
		,IsNull((Select CompanyName From Company Where Id=opr.CompanyId),''---'') As CompanyName
	FROM
		Order_PaymentRecord opr,[Order] o where o.oid=opr.Oid  '+@str+' 
		FinanceDate between @sRq and @eRq
		)
		
		 select *,(select count(1) from list) as cid,(select sum(Prices) from list) as zje 
		        from list where Row Between '+CONVERT(VARCHAR,@StartIndex)+' and '+CONVERT(VARCHAR,@EndIndex)+'
		 order by row
		'
		

		
	--PRINT(@Sqlstr)
	EXEC(@Sqlstr)	
		
END
